Advancing the discovery of unique column combinations (Technische Berichte des Hasso-Plattner-Instituts für Softwaresystemtechnik ; 51)

Unique column combinations of a relational database table are sets of columns that contain only unique values. Discovering such combinations is a fundamental research problem and has many different data management and knowledge discovery applications. Existing discovery algorithms are either brute force or have a high memory load and can thus be applied only to small datasets or samples. In this paper, the wellknown Gordian algorithm [19] and “Apriori-based” algorithms [5] are compared and analyzed for further optimization. We greatly improve the Apriori algorithms through efficient candidate generation and statisticsbased pruning methods. A hybrid solution HCA-Gordian combines the advantages of Gordian and our new algorithm HCA, and it significantly outperforms all previous work in many situations. 1 Unique Column Combinations Unique column combinations are sets of columns of a relational database table that fulfill the uniqueness constraint. Uniqueness of a column combination K within a table can be defined as follows: Definition 1 Given a relational database schema R = {C1, C2, . . . , Cm} with columns Ci and an instance r ⊆ C1 × . . . × Cm, a column combination K ⊆ R is a unique, iff ∀t1, t2 ∈ r : (t1 = t2) ⇒ (t1[K] = t2[K]) Discovered uniques are good candidates for primary keys of a table. Therefore some literature refers to them as as “candidate keys” [17]. The term “composite key” is also used to highlight the fact that they comprise multiple columns [19]. We want to stress that the detection of uniques is a problem that can be solved exactly, while the detection of keys can only be solved heuristically. Uniqueness is a necessary precondition for a key, but only a human expert can “promote” a unique to a key, because uniques can appear by coincidence for a certain state of the data. In contrast, keys are consciously specified and denote a schema constraint. An important property of uniques and keys is their minimality. Minimal uniques are uniques of which no strict subsets hold the uniqueness property: